package br.edu.universo.si.projeto2.infojob.server.persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import br.edu.universo.si.projeto2.infojob.client.bean.BeanEmpresaAssinante;
import br.edu.universo.si.projeto2.infojob.client.bean.BeanEstado;
import br.edu.universo.si.projeto2.infojob.client.bean.BeanProfissionalCurriculo;
import br.edu.universo.si.projeto2.infojob.client.bean.BeanProfissionalAssinante;
import br.edu.universo.si.projeto2.infojob.client.bean.BeanTitularidade;
import br.edu.universo.si.projeto2.infojob.client.bean.BeanVaga;
import br.edu.universo.si.projeto2.infojob.server.Database;
import br.edu.universo.si.projeto2.infojob.server.excecoes.ContaExistenteException;
import br.edu.universo.si.projeto2.infojob.server.persistencia.PCurriculo;
import br.edu.universo.si.projeto2.infojob.server.persistencia.PProfissional;
import br.edu.universo.si.projeto2.infojob.server.excecoes.CurriculoEnviadoException;

/**
 * Classe de persistência de vagas.
 * @author Marcelo M. Fleury
 */
public class PVagas
{
	/**
	 * Busca as vagas ofertadas.
	 * @author Marcelo M. Fleury
	 * @param filtro : um bean de Vaga com todos os filtros
	 * @return ArrayList<BeanVaga> : lista com todas as vagas de acordo com a filtragem
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ArrayList<BeanVaga> buscarVagas(BeanVaga filtro) throws ClassNotFoundException, SQLException
	{
		ArrayList<BeanVaga> clVagas = new ArrayList<BeanVaga>();

		Database database = Database.getInstance();

		Integer empresa_id = filtro.getBEmpresaAssinante().getId();
		String empresa = filtro.getBEmpresaAssinante().getRazaoSocial();
		String cargo = filtro.getCargo();
		Boolean isEncerrada = filtro.getEncerrada();
		Integer cargaHorariaSemanal = filtro.getCargaHorariaSemanal();
		Double remuneracao = filtro.getRemuneracao();
		String cidade = filtro.getCidade();
		Integer estado = filtro.getBEstado().getId();

		String query =
				"SELECT vagas.*, empresa_assinante.razao_social," +
					"to_char(vagas.data_criacao, 'dd/mm/YYYY') AS data_criacao_str, to_char(vagas.data_encerramento, 'dd/mm/YYYY') AS data_encerramento_str " + 
				"FROM infojob.tb_empresa_assinante AS empresa_assinante, infojob.tb_vagas AS vagas " +
				"WHERE empresa_assinante.id = vagas.id_empresa ";

		if (empresa != null && !empresa.equals(""))
		{
			query += "AND replace(lower(empresa_assinante.razao_social), ' ', '') like('%" + empresa.trim().toLowerCase()  + "%')";
		}

		if (cargo != null && !cargo.equals(""))
		{
			query += "AND replace(lower(vagas.cargo), ' ', '') like('%" + cargo.trim().toLowerCase() + "%') ";
		}

		if (cidade != null && !cidade.equals(""))
		{
			query += "AND lower(vagas.cidade) like('%" + cidade.trim().toLowerCase() + "%') ";
		}

		if (cargaHorariaSemanal != null && cargaHorariaSemanal >= 20)
		{
			query += "AND vagas.ch_semanal = " + cargaHorariaSemanal;
		}

		if (remuneracao != null && remuneracao >= 1000.00)
		{
			query += "AND vagas.remuneracao between " + (remuneracao - 1000.00) + " AND " + (remuneracao + 1000.00);
		}

		if (estado != null && estado > 0)
		{
			query += "AND vagas.id_estado = " + estado;
		}

		if (empresa_id != null && empresa_id > 0)
		{
			query += "AND empresa_assinante.id = " + empresa_id;
		}

		if (isEncerrada != null)
		{
			query += "AND vagas.encerrada = " + isEncerrada.booleanValue();
		}
		
		Statement stmt = database.getStatement();

		ResultSet rs = stmt.executeQuery(query);

		while (rs.next())
		{
			BeanVaga bVaga = new BeanVaga();

			bVaga.setId(rs.getInt("id"));

			BeanEmpresaAssinante bEmpresaAssinante = PEmpresa.getEmpresaById(rs.getInt("id_empresa"));

			bVaga.setBEmpresaAssinante(bEmpresaAssinante);
			bVaga.setCargo(rs.getString("cargo"));
			bVaga.setCargaHorariaSemanal(rs.getInt("ch_semanal"));
			bVaga.setRemuneracao(rs.getDouble("remuneracao"));

			BeanTitularidade bTitularidade = PTitularidade.getTitularidadeById(rs.getInt("id_titularidade"));
			bVaga.setBTitularidade(bTitularidade);

			bVaga.setAtribuicoes(rs.getString("atribuicoes"));
			bVaga.setCidade(rs.getString("cidade"));

			BeanEstado bEstado = PEstado.getEstadoById(rs.getInt("id_estado"));
			bVaga.setBEstado(bEstado);

			bVaga.setExperiencia(rs.getInt("experiencia"));
			bVaga.setRequisitos(rs.getString("requisitos"));
			bVaga.setEncerrada(rs.getBoolean("encerrada"));
			bVaga.setDataCriacao(rs.getString("data_criacao_str"));
			bVaga.setDataEncerramento(rs.getString("data_encerramento_str"));

			clVagas.add(bVaga);
		}
		return clVagas;
	}

	/**
	 * Busca uma vaga pelo ID.
	 * @author Marcelo M. Fleury
	 * @param idVaga
	 * @return BeanVaga : bean de vaga completo.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static BeanVaga getVagaById(Integer idVaga) throws ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();

		PreparedStatement pStmt = database.getPreparedStatement(
				"SELECT *, to_char(data_criacao, 'dd/mm/YYYY') AS data_criacao_str, " +
					"to_char(data_encerramento, 'dd/mm/YYYY') AS data_encerramento_str " +
				"FROM infojob.tb_vagas WHERE id = ?; ");

		pStmt.setInt(1, idVaga);

		ResultSet rs = pStmt.executeQuery();

		if (rs.next())
		{
			BeanVaga vaga = new BeanVaga();

			vaga.setId(rs.getInt("id"));

			BeanEmpresaAssinante bEmpresaAssinante = PEmpresa.getEmpresaById(rs.getInt("id_empresa"));

			vaga.setBEmpresaAssinante(bEmpresaAssinante);
			vaga.setCargo(rs.getString("cargo"));
			vaga.setCargaHorariaSemanal(rs.getInt("ch_semanal"));
			vaga.setRemuneracao(rs.getDouble("remuneracao"));

			BeanTitularidade bTitularidade = PTitularidade.getTitularidadeById(rs.getInt("id_titularidade"));
			vaga.setBTitularidade(bTitularidade);

			vaga.setAtribuicoes(rs.getString("atribuicoes"));
			vaga.setCidade(rs.getString("cidade"));

			BeanEstado bEstado = PEstado.getEstadoById(rs.getInt("id_estado"));
			vaga.setBEstado(bEstado);

			vaga.setExperiencia(rs.getInt("experiencia"));
			vaga.setRequisitos(rs.getString("requisitos"));
			vaga.setEncerrada(rs.getBoolean("encerrada"));
			vaga.setDataCriacao(rs.getString("data_criacao_str"));
			vaga.setDataEncerramento(rs.getString("data_encerramento_str"));

			return vaga;
		}

		return null;
	}
	
	/**
	 * Cadastra uma vaga no banco.
	 * @author Marcelo M. Fleury
	 * @param bVaga
	 * @throws ClassNotFoundException
	 * @throws ContaExistenteException
	 * @throws Exception
	 */
	public static void cadastrarVaga(BeanVaga bVaga)
		throws ClassNotFoundException, ContaExistenteException, Exception
	{
		Database database = Database.getInstance();

		PreparedStatement pStmt = database.getPreparedStatement("INSERT INTO infojob.tb_vagas(id_empresa, cargo, ch_semanal, remuneracao, " +
				"id_titularidade, atribuicoes, cidade, id_estado, experiencia, requisitos, " +
				"encerrada, data_criacao, data_encerramento) " +
				"VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_DATE, to_date(?, 'dd/mm/YYYY'))");

		pStmt.setString(2, bVaga.getCargo());
		pStmt.setInt(3, bVaga.getCargaHorariaSemanal());
		pStmt.setDouble(4, bVaga.getRemuneracao());
		pStmt.setString(6, bVaga.getAtribuicoes());
		pStmt.setString(7, bVaga.getCidade());
		pStmt.setInt(9, bVaga.getExperiencia());
		pStmt.setString(10, bVaga.getRequisitos());
		pStmt.setBoolean(11, bVaga.getEncerrada());
		//pStmt.setString(12, "CURRENT_DATE");
		pStmt.setString(12, bVaga.getDataEncerramento());
		
		if (bVaga.getBEmpresaAssinante().getId() == -1)
			pStmt.setNull(1, java.sql.Types.NULL);
		else
			pStmt.setInt(1, bVaga.getBEmpresaAssinante().getId());

		if (bVaga.getBTitularidade().getId() == -1)
			pStmt.setNull(5, java.sql.Types.NULL);
		else
			pStmt.setInt(5, bVaga.getBTitularidade().getId());

		if (bVaga.getBEstado().getId() == -1)
			pStmt.setNull(8, java.sql.Types.NULL);
		else
			pStmt.setInt(8, bVaga.getBEstado().getId());
		
		pStmt.executeUpdate();
	}

	/**
	 * Altera os dados de uma vaga.
	 * @author Marcelo M. Fleury
	 * @param bVaga
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void alterarVaga(BeanVaga bVaga) throws ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();
		
		PreparedStatement pStmt = database.getPreparedStatement("UPDATE infojob.tb_vagas " +
				"SET cargo = ?, ch_semanal = ?, remuneracao = ?, id_titulatidade, atribuicoes, cidade " +
				"id_estado, experiencia, requisitos, encerrada, data_encerramento = to_date(?, 'dd/mm/YYYY'), WHERE id = ?");
		
		pStmt.setString(1, bVaga.getCargo());
		pStmt.setInt(2, bVaga.getCargaHorariaSemanal());
		pStmt.setDouble(3, bVaga.getRemuneracao());
		pStmt.setString(5, bVaga.getAtribuicoes());
		pStmt.setString(6, bVaga.getCidade());
		pStmt.setInt(8, bVaga.getExperiencia());
		pStmt.setString(9, bVaga.getRequisitos());
		pStmt.setBoolean(10, bVaga.getEncerrada());
		pStmt.setString(11, bVaga.getDataEncerramento());
		pStmt.setInt(12, bVaga.getId());

		if (bVaga.getBTitularidade().getId() == -1)
			pStmt.setNull(4, java.sql.Types.NULL);
		else
			pStmt.setInt(4, bVaga.getBTitularidade().getId());

		if (bVaga.getBEstado().getId() == -1)
			pStmt.setNull(7, java.sql.Types.NULL);
		else
			pStmt.setInt(7, bVaga.getBEstado().getId());
		

		pStmt.executeUpdate();	
	}

	/**
	 * Apaga uma vaga pelo ID.
	 * @author Marcelo M. Fleury
	 * @param idVaga
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void deletarVaga(Integer idVaga) throws ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();
		
		PreparedStatement pStmt = database.getPreparedStatement("DELETE FROM infojob.tb_vagas " +
				"WHERE id= ?");
		
		pStmt.setInt(1, idVaga);

		pStmt.executeUpdate();	
	}
	
	/**
	 * Envia um currículo para a vaga, ou seja, adiciona mais um concorrente para a vaga.
	 * @author Marcelo M. Fleury
	 * @param vaga
	 * @param bCurriculo
	 * @throws CurriculoEnviadoException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void inserirCandidato(BeanVaga vaga, BeanProfissionalCurriculo bCurriculo)
		throws CurriculoEnviadoException, ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();

		PreparedStatement pStmt = database.getPreparedStatement(
				"INSERT INTO infojob.tb_vagas_candidatos(id_vaga, id_curriculo, data_envio) VALUES(?, ?, CURRENT_DATE); ");

		pStmt.setInt(1, vaga.getId());
		pStmt.setInt(2, bCurriculo.getId());

		pStmt.executeUpdate();
	}
	
	/**
	 * Encerra todas as vagas ofertadas por uma empresa.
	 * @author Marcelo M. Fleury
	 * @param bEmpresaAssinante
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void encerrarVagasEmpresa(BeanEmpresaAssinante bEmpresaAssinante)
		throws ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();

		PreparedStatement pStmt = database.getPreparedStatement("UPDATE infojob.tb_vagas " +
				"SET encerrada = true, data_encerramento = CURRENT_DATE WHERE id_empresa = ?; ");
		pStmt.setInt(1, bEmpresaAssinante.getId());

		pStmt.executeUpdate();
	}
	
	/**
	 * Verifica se o currículo já foi enviado para a vaga.
	 * @author Marcelo M. Fleury
	 * @param vaga
	 * @param bCurriculo
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean verificarEnvioCurriculo(BeanVaga vaga, BeanProfissionalCurriculo bCurriculo)
		throws ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();

		PreparedStatement pStmt = database.getPreparedStatement(
				"SELECT * FROM infojob.tb_vagas_candidatos " +
				"WHERE id_vaga = ? AND id_curriculo = ?; ");

		pStmt.setInt(1, vaga.getId());
		pStmt.setInt(2, bCurriculo.getId());

		ResultSet rs = pStmt.executeQuery();

		if (rs.next())
		{
			return true;
		}

		return false;
	}

	/**
	 * Exclui uma vaga com controle transacional.
	 * @author Marcelo M. Fleury
	 * @param conn
	 * @param vaga
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	
	public static void excluirVaga(int idVaga) throws ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();
		
		PreparedStatement pStmt = database.getPreparedStatement("DELETE FROM infojob.tb_vagas " +
				"WHERE id= ?");
		
		pStmt.setInt(1, idVaga);

		pStmt.executeUpdate();	
	}
	
	public static void encerrarVaga(BeanVaga bVaga) throws ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();
		
		PreparedStatement pStmt = database.getPreparedStatement("UPDATE infojob.tb_vagas " +
				"SET encerrada = true, data_encerramento = CURRENT_DATE WHERE id = ?");
		
		pStmt.setInt(1, bVaga.getId());

		pStmt.executeUpdate();	
	}

	/**
	 * Excluir todas as relações de VAGA - CURRÍCULO daquela vaga. Tem controle transacional.
	 * @author Marcelo M. Fleury
	 * @param conn
	 * @param vaga
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void excluirVagaCandidato(Connection conn, BeanVaga vaga)
		throws ClassNotFoundException, SQLException
	{
		Database database = Database.getInstance();

		PreparedStatement pstmt = database.getPreparedStatement(conn,
				"DELETE FROM infojob.tb_vagas_candidatos WHERE id_vaga = ?; ");
		pstmt.setInt(1, vaga.getId());

		pstmt.executeUpdate();		
	}

	/**
	 * Busca todas as vagas de uma empresa com controle transacional.
	 * @author Marcelo M. Fleury
	 * @param conn
	 * @param bEmpresaAssinante
	 * @return ArrayList<BeanVaga> : lista com todas as vagas.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ArrayList<BeanVaga> getTodasVagasDaEmpresa(Connection conn, BeanEmpresaAssinante bEmpresaAssinante)
		throws ClassNotFoundException, SQLException
	{
		ArrayList<BeanVaga> clVagas = new ArrayList<BeanVaga>();

		Database database = Database.getInstance();

		PreparedStatement pstmt = database.getPreparedStatement(conn,
				"SELECT *, TO_CHAR(data_criacao, 'dd/mm/YYYY') AS data_criacao_str, " +
				"TO_CHAR(data_encerramento, 'dd/mm/YYYY') AS data_encerramento_str " +
				"FROM infojob.tb_vagas WHERE id_empresa = ?; ");
		pstmt.setInt(1, bEmpresaAssinante.getId());

		ResultSet rs = pstmt.executeQuery();

		while (rs.next())
		{
			BeanVaga vaga = new BeanVaga();

			vaga.setId(rs.getInt("id"));
			vaga.setBEmpresaAssinante(PEmpresa.getEmpresaById(rs.getInt("id_empresa")));
			vaga.setCargo(rs.getString("cargo"));
			vaga.setCargaHorariaSemanal(rs.getInt("ch_semanal"));
			vaga.setRemuneracao(rs.getDouble("remuneracao"));
			vaga.setBTitularidade(PTitularidade.getTitularidadeById(rs.getInt("id_titularidade")));
			vaga.setAtribuicoes(rs.getString("atribuicoes"));
			vaga.setCidade(rs.getString("cidade"));
			vaga.setBEstado(PEstado.getEstadoById(rs.getInt("id_estado")));
			vaga.setExperiencia(rs.getInt("experiencia"));
			vaga.setRequisitos(rs.getString("requisitos"));
			vaga.setEncerrada(rs.getBoolean("encerrada"));
			vaga.setDataCriacao(rs.getString("data_criacao_str"));
			vaga.setDataEncerramento(rs.getString("data_encerramento_str"));

			clVagas.add(vaga);
		}

		return clVagas;
	}
	
	/**
	 * Busca todos os cv de uma vaga
	 * @author Marcelo M. Fleury
	 * @param vagaId
	 * @return ArrayList<BeanProfissionalAssinante> : todos os cv de uma vaga.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static ArrayList<BeanProfissionalAssinante> getProfissionaisByVaga(int vagaId)
		throws ClassNotFoundException, SQLException
	{
		ArrayList<BeanProfissionalAssinante> clProfissionais = new ArrayList<BeanProfissionalAssinante>();

		Database database = Database.getInstance();

		PreparedStatement pstmt = database.getPreparedStatement("SELECT id_profissional FROM infojob.tb_vagas_candidatos " +
				"INNER JOIN infojob.tb_profissional_curriculo ON tb_vagas_candidatos.id_curriculo=tb_profissional_curriculo.id " +
				"WHERE tb_vagas_candidatos.id_vaga = ?");
		pstmt.setInt(1, vagaId);

		ResultSet rs = pstmt.executeQuery();

		while (rs.next())
		{
			BeanProfissionalAssinante profissional = PProfissional.getProfissionalById(rs.getInt("id_profissional"));
			clProfissionais.add(profissional);
		}

		return clProfissionais;
	}
}